import os
import re
from datetime import datetime, timedelta

import pandas as pd
from openpyxl import load_workbook, __version__ as openpyxl_version
from openpyxl.styles import Alignment, PatternFill, Font, Border, Side
from openpyxl.cell.cell import MergedCell

from UserRecord import UserRecord

# 检查 openpyxl 版本
required_openpyxl_version = '3.0.0'
if openpyxl_version < required_openpyxl_version:
    raise ImportError(f"openpyxl 版本过低，请升级到 {required_openpyxl_version} 或更高版本。当前版本: {openpyxl_version}")

# 读取配置文件
config = {}
with open('config.txt', 'r', encoding='utf-8') as file:  # 指定编码为 utf-8
    for line in file:
        line = line.strip()
        if line and not line.startswith('#') and '=' in line:
            key, value = line.split('=', 1)  # 使用 split('=', 1) 确保只分割一次
            config[key] = value
        elif line and not line.startswith('#'):
            print(f"# 警告: 跳过不符合格式的行: {line}")

# 将配置信息转换为变量
DATA_DIR = config['DATA_DIR']
FILE_NAME_PATTERN = config['FILE_NAME_PATTERN']
BASE_FILE_NAME = config['BASE_FILE_NAME']
DAILY_USAGE_DF_LIMIT = int(config['DAILY_USAGE_DF_LIMIT'])
CODE_SUBMISSION_DETAIL_LIMIT = int(config['CODE_SUBMISSION_DETAIL_LIMIT'])

# 工作表名称
SHEET_YESTERDAY_DETAIL = '昨日明细'
SHEET_YESTERDAY_DATA = '昨日累计'
SHEET_DAILY_DETAIL = '每日明细'
SHEET_DAILY_USAGE = '使用次数明细'
SHEET_CODE_SUBMISSION_DETAIL = '提交代码行数明细'
SHEET_PERSONNEL_INFO = '人员信息'

# 列名
COLUMN_RESPONSIBLE = '负责人'
COLUMN_PRODUCT_LINE = '产品线'
COLUMN_UM = 'UM'
COLUMN_NAME = '姓名'
COLUMN_USAGE_COUNT = '使用次数'
COLUMN_CODE_SUBMISSION_COUNT = '提交代码行数'
COLUMN_CODE_SUBMISSION_AI_RATIO = '提交代码 AI 代码占比'

# 打印当前工作目录
print(f"当前工作目录: {os.getcwd()}")

class ExcelUpdater:
    def __init__(self, file_path, update_file_name, formatted_date):
        self.file_path = file_path
        self.update_file_name = update_file_name
        self.formatted_date = formatted_date
        self.yesterday_data_map = {}
        self.previous_day_data_map = {}
        self.yesterday_detail_map = {}
        self.personnel_info_map = {}

    def read_yesterday_data(self):
        if os.path.exists(self.file_path):
            df = pd.read_excel(self.file_path)
            for _, row in df.iterrows():
                user_record = UserRecord(
                    部门=row['部门'],
                    分组=row['分组'],
                    L3_领域=row['L3 领域'],
                    UM=row[COLUMN_UM],
                    姓名=row[COLUMN_NAME],
                    使用次数=row[COLUMN_USAGE_COUNT],
                    入库代码行数=row['入库代码行数'],
                    生成代码行数=row['生成代码行数'],
                    入库率=row['入库率'],
                    提交代码行数=row[COLUMN_CODE_SUBMISSION_COUNT],
                    提交代码_AI_代码占比=row[COLUMN_CODE_SUBMISSION_AI_RATIO]
                )
                self.yesterday_data_map[row[COLUMN_UM]] = user_record
        else:
            print(f"文件 {self.file_path} 不存在")
            # 提供更详细的错误信息
            print("请检查以下几点：")
            print(f"1. 确认文件 {os.path.basename(self.file_path)} 是否存在于路径 {os.path.dirname(self.file_path)} 中。")
            print("2. 确认文件名拼写是否正确。")
            print("3. 如果文件名格式不同，请修改代码中的日期格式或文件名。")

    def read_previous_day_data(self, data_dir, file_pattern):
        latest_date = None
        latest_file_path = None
        for file_name in os.listdir(data_dir):
            match = file_pattern.match(file_name)
            if match:
                date_str = match.group(1)
                file_date = datetime.strptime(date_str, '%m-%d')
                if file_date < datetime.now() - timedelta(days=1):
                    if latest_date is None or file_date > latest_date:
                        latest_date = file_date
                        latest_file_path = os.path.join(data_dir, file_name)

        if latest_file_path:
            print(f"找到最近的文件: {latest_file_path}")
            df = pd.read_excel(latest_file_path)
            for _, row in df.iterrows():
                user_record = UserRecord(
                    部门=row['部门'],
                    分组=row['分组'],
                    L3_领域=row['L3 领域'],
                    UM=row[COLUMN_UM],
                    姓名=row[COLUMN_NAME],
                    使用次数=row[COLUMN_USAGE_COUNT],
                    入库代码行数=row['入库代码行数'],
                    生成代码行数=row['生成代码行数'],
                    入库率=row['入库率'],
                    提交代码行数=row[COLUMN_CODE_SUBMISSION_COUNT],
                    提交代码_AI_代码占比=row[COLUMN_CODE_SUBMISSION_AI_RATIO]
                )
                self.previous_day_data_map[row[COLUMN_UM]] = user_record

    def calculate_yesterday_detail(self):
        for um, yesterday_record in self.yesterday_data_map.items():
            previous_day_record = self.previous_day_data_map.get(um)
            if previous_day_record:
                detail_record = UserRecord(
                    部门=yesterday_record.部门,
                    分组=yesterday_record.分组,
                    L3_领域=yesterday_record.L3_领域,
                    UM=yesterday_record.UM,
                    姓名=yesterday_record.姓名,
                    使用次数=yesterday_record.使用次数 - previous_day_record.使用次数,
                    入库代码行数=yesterday_record.入库代码行数 - previous_day_record.入库代码行数,
                    生成代码行数=yesterday_record.生成代码行数 - previous_day_record.生成代码行数,
                    入库率=yesterday_record.入库率 - previous_day_record.入库率,
                    提交代码行数=yesterday_record.提交代码行数 - previous_day_record.提交代码行数,
                    提交代码_AI_代码占比=yesterday_record.提交代码_AI_代码占比 - previous_day_record.提交代码_AI_代码占比
                )
            else:
                detail_record = yesterday_record
            self.yesterday_detail_map[um] = detail_record

    def read_personnel_info(self):
        try:
            personnel_info_df = pd.read_excel(self.update_file_name, sheet_name=SHEET_PERSONNEL_INFO, index_col=None)
        except ValueError:
            print(f"未找到 '{SHEET_PERSONNEL_INFO}' 工作表，创建新的 '{SHEET_PERSONNEL_INFO}' 工作表")
            personnel_info_df = pd.DataFrame(columns=[COLUMN_UM])

        for _, row in personnel_info_df.iterrows():
            um = row[COLUMN_UM]
            self.personnel_info_map[um] = row.to_dict()

    def update_sheets(self):
        self.update_yesterday_detail_sheet()
        self.update_yesterday_data_sheet()
        self.update_daily_detail_sheet()
        self.update_daily_usage_sheet()
        self.update_code_submission_detail_sheet()

    def update_yesterday_detail_sheet(self):
        yesterday_detail_df = pd.DataFrame([record.__dict__ for record in self.yesterday_detail_map.values()])
        yesterday_detail_df.insert(0, COLUMN_RESPONSIBLE, yesterday_detail_df[COLUMN_UM].map(self.personnel_info_map.get).map(lambda x: x.get(COLUMN_RESPONSIBLE, '')))
        yesterday_detail_df.insert(1, COLUMN_PRODUCT_LINE, yesterday_detail_df[COLUMN_UM].map(self.personnel_info_map.get).map(lambda x: x.get(COLUMN_PRODUCT_LINE, '')))

        with pd.ExcelWriter(self.update_file_name, mode='a', if_sheet_exists='replace') as writer:
            yesterday_detail_df.to_excel(writer, sheet_name=SHEET_YESTERDAY_DETAIL, index=False)

        wb = load_workbook(self.update_file_name)
        wb.save(self.update_file_name)
        print(f"sheet: {SHEET_YESTERDAY_DETAIL}, 数据已更新到文件: {self.update_file_name}")

    def update_yesterday_data_sheet(self):
        yesterday_data_df = pd.DataFrame([record.__dict__ for record in self.yesterday_data_map.values()])
        yesterday_data_df.insert(0, COLUMN_RESPONSIBLE, yesterday_data_df[COLUMN_UM].map(self.personnel_info_map.get).map(lambda x: x.get(COLUMN_RESPONSIBLE, '')))
        yesterday_data_df.insert(1, COLUMN_PRODUCT_LINE, yesterday_data_df[COLUMN_UM].map(self.personnel_info_map.get).map(lambda x: x.get(COLUMN_PRODUCT_LINE, '')))

        with pd.ExcelWriter(self.update_file_name, mode='a', if_sheet_exists='replace') as writer:
            yesterday_data_df.to_excel(writer, sheet_name=SHEET_YESTERDAY_DATA, index=False)

        wb = load_workbook(self.update_file_name)
        wb.save(self.update_file_name)
        print(f"sheet: {SHEET_YESTERDAY_DATA}, 数据已更新到文件: {self.update_file_name}")

    def update_daily_detail_sheet(self):
        try:
            daily_detail_df = pd.read_excel(self.update_file_name, sheet_name=SHEET_DAILY_DETAIL, header=[0, 1], index_col=None)
        except ValueError:
            print(f"未找到 '{SHEET_DAILY_DETAIL}' 工作表，创建新的 '{SHEET_DAILY_DETAIL}' 工作表")
            daily_detail_df = pd.DataFrame(columns=pd.MultiIndex.from_product([[COLUMN_UM], [COLUMN_UM]]))

        yesterday_detail_header = pd.MultiIndex.from_tuples([
            (self.formatted_date, COLUMN_USAGE_COUNT),
            (self.formatted_date, '入库代码行数'),
            (self.formatted_date, '生成代码行数'),
            (self.formatted_date, '入库率'),
            (self.formatted_date, COLUMN_CODE_SUBMISSION_COUNT),
            (self.formatted_date, COLUMN_CODE_SUBMISSION_AI_RATIO)
        ])

        new_columns = pd.DataFrame(index=daily_detail_df.index, columns=yesterday_detail_header)

        for um, record in self.yesterday_detail_map.items():
            if um in daily_detail_df.index:
                new_columns.loc[um] = [record.使用次数, record.入库代码行数, record.生成代码行数, record.入库率, record.提交代码行数, record.提交代码_AI_代码占比]

        daily_detail_df = pd.concat([daily_detail_df.iloc[:, :7], new_columns, daily_detail_df.iloc[:, 7:]], axis=1)

        wb = load_workbook(self.update_file_name)
        ws = wb[SHEET_DAILY_DETAIL]

        merged_ranges = list(ws.merged_cells.ranges)
        for merged_range in merged_ranges:
            ws.unmerge_cells(str(merged_range))

        header_row = 1
        for col_idx, col_name in enumerate(daily_detail_df.columns, start=1):
            if isinstance(col_name, tuple):
                ws.cell(row=header_row, column=col_idx, value=col_name[0])
                ws.cell(row=header_row + 1, column=col_idx, value=col_name[1])
            else:
                ws.cell(row=header_row, column=col_idx, value=col_name)
                ws.cell(row=header_row + 1, column=col_idx, value='')

        ws.merge_cells(start_row=header_row, start_column=1, end_row=header_row, end_column=7)
        ws.cell(row=header_row, column=1, value='人员信息').alignment = Alignment(horizontal='center', vertical='center')

        start_col = 8
        while start_col <= ws.max_column:
            end_col = start_col + 5
            if end_col > ws.max_column:
                end_col = ws.max_column
            ws.merge_cells(start_row=header_row, start_column=start_col, end_row=header_row, end_column=end_col)
            ws.cell(row=header_row, column=start_col, value=ws.cell(row=header_row, column=start_col).value).alignment = Alignment(horizontal='center', vertical='center')
            start_col += 6

        for index, row in daily_detail_df.iterrows():
            um = row.get('人员信息').get(COLUMN_UM)
            yesterday_detail = self.yesterday_detail_map.get(um)
            personnel_info = self.personnel_info_map.get(um)
            if personnel_info:
                if daily_detail_df[('人员信息', COLUMN_RESPONSIBLE)].dtype != object:
                    daily_detail_df[('人员信息', COLUMN_RESPONSIBLE)] = daily_detail_df[('人员信息', COLUMN_RESPONSIBLE)].astype(object)
                if daily_detail_df[('人员信息', COLUMN_PRODUCT_LINE)].dtype != object:
                    daily_detail_df[('人员信息', COLUMN_PRODUCT_LINE)] = daily_detail_df[('人员信息', COLUMN_PRODUCT_LINE)].astype(object)

                daily_detail_df.at[index, ('人员信息', COLUMN_RESPONSIBLE)] = personnel_info.get(COLUMN_RESPONSIBLE)
                daily_detail_df.at[index, ('人员信息', COLUMN_PRODUCT_LINE)] = personnel_info.get(COLUMN_PRODUCT_LINE)
            if yesterday_detail:
                for header in yesterday_detail_header:
                    if header[0] == self.formatted_date:
                        daily_detail_df.at[index, header] = yesterday_detail.__dict__.get(header[1])

        for row_idx, row in enumerate(daily_detail_df.values, start=header_row + 2):
            for col_idx, value in enumerate(row, start=1):
                ws.cell(row=row_idx, column=col_idx, value=value)

        wb.save(self.update_file_name)
        print(f"sheet: {SHEET_DAILY_DETAIL}, 数据已更新到文件: {self.update_file_name}")

    def update_daily_usage_sheet(self):
        try:
            daily_usage_df = pd.read_excel(self.update_file_name, sheet_name=SHEET_DAILY_USAGE, index_col=None)
        except ValueError:
            print(f"未找到 '{SHEET_DAILY_USAGE}' 工作表，创建新的 '{SHEET_DAILY_USAGE}' 工作表")
            daily_usage_df = pd.DataFrame(columns=[COLUMN_UM])

        # 修正负责人和产品线的引用
        daily_usage_df[COLUMN_RESPONSIBLE] = daily_usage_df[COLUMN_UM].map(self.personnel_info_map.get).map(lambda x: x.get(COLUMN_RESPONSIBLE, ''))
        daily_usage_df[COLUMN_PRODUCT_LINE] = daily_usage_df[COLUMN_UM].map(self.personnel_info_map.get).map(lambda x: x.get(COLUMN_PRODUCT_LINE, ''))

        daily_usage_df.insert(7, self.formatted_date, 0)

        for index, row in daily_usage_df.iterrows():
            um = row.get(COLUMN_UM)
            yesterday_detail = self.yesterday_detail_map.get(um)
            if yesterday_detail:
                daily_usage_df.at[index, self.formatted_date] = yesterday_detail.使用次数

        wb = load_workbook(self.update_file_name)
        ws = wb[SHEET_DAILY_USAGE]

        ws.delete_rows(1, ws.max_row)

        header_row = 1
        for col_idx, col_name in enumerate(daily_usage_df.columns, start=1):
            ws.cell(row=header_row, column=col_idx, value=col_name)

        for row_idx, row in enumerate(daily_usage_df.values, start=header_row + 1):
            for col_idx, value in enumerate(row, start=1):
                ws.cell(row=row_idx, column=col_idx, value=value)

        wb.save(self.update_file_name)
        print(f"sheet: {SHEET_DAILY_USAGE}, 数据已更新到文件: {self.update_file_name}")

        red_fill = PatternFill(start_color="FF0000", end_color="FF0000", fill_type="solid")

        for index, row in daily_usage_df.iterrows():
            for col_idx, col_name in enumerate(daily_usage_df.columns[7:], start=8):
                if row[col_name] < DAILY_USAGE_DF_LIMIT:
                    ws.cell(row=index + 2, column=col_idx).fill = red_fill

        wb.save(self.update_file_name)
        print(f"sheet: {SHEET_DAILY_USAGE}, 数据已更新到文件: {self.update_file_name}")

    def update_code_submission_detail_sheet(self):
        try:
            code_submission_detail = pd.read_excel(self.update_file_name, sheet_name=SHEET_CODE_SUBMISSION_DETAIL, index_col=None)
        except ValueError:
            print(f"未找到 '{SHEET_CODE_SUBMISSION_DETAIL}' 工作表，创建新的 '{SHEET_CODE_SUBMISSION_DETAIL}' 工作表")
            code_submission_detail = pd.DataFrame(columns=[COLUMN_UM])

        code_submission_detail[COLUMN_RESPONSIBLE] = code_submission_detail[COLUMN_UM].map(self.personnel_info_map.get).map(lambda x: x.get(COLUMN_RESPONSIBLE, ''))
        code_submission_detail[COLUMN_PRODUCT_LINE] = code_submission_detail[COLUMN_UM].map(self.personnel_info_map.get).map(lambda x: x.get(COLUMN_PRODUCT_LINE, ''))

        code_submission_detail.insert(7, self.formatted_date, 0)

        for index, row in code_submission_detail.iterrows():
            um = row.get(COLUMN_UM)
            yesterday_detail = self.yesterday_detail_map.get(um)
            if yesterday_detail:
                code_submission_detail.at[index, self.formatted_date] = yesterday_detail.提交代码行数

        wb = load_workbook(self.update_file_name)
        ws = wb[SHEET_CODE_SUBMISSION_DETAIL]

        ws.delete_rows(1, ws.max_row)

        header_row = 1
        for col_idx, col_name in enumerate(code_submission_detail.columns, start=1):
            ws.cell(row=header_row, column=col_idx, value=col_name)

        for row_idx, row in enumerate(code_submission_detail.values, start=header_row + 1):
            for col_idx, value in enumerate(row, start=1):
                ws.cell(row=row_idx, column=col_idx, value=value)

        wb.save(self.update_file_name)
        print(f"sheet: {SHEET_CODE_SUBMISSION_DETAIL}, 数据已更新到文件: {self.update_file_name}")

        red_fill = PatternFill(start_color="FF0000", end_color="FF0000", fill_type="solid")

        date_columns = [col_name for col_name in code_submission_detail.columns[7:] if isinstance(col_name, str)]
        weekly_columns = {}

        for date_col in date_columns:
            try:
                month, day = map(int, date_col.split('-'))
                current_year = datetime.now().year
                date = datetime(current_year, month, day)
                week_key = f"{date.isocalendar().year}-{date.isocalendar().week}"
                if week_key not in weekly_columns:
                    weekly_columns[week_key] = []
                weekly_columns[week_key].append(date_col)
            except ValueError:
                continue

        for index, row in code_submission_detail.iterrows():
            for week_key, columns_in_week in weekly_columns.items():
                total = 0
                count = 0
                for col_name in columns_in_week:
                    try:
                        value = row[col_name]
                        if pd.notna(value):
                            total += value
                            count += 1
                    except KeyError:
                        continue
                if count > 0:
                    average = total / count
                    if average < CODE_SUBMISSION_DETAIL_LIMIT:
                        for col_name in columns_in_week:
                            col_idx = list(code_submission_detail.columns).index(col_name) + 1
                            ws.cell(row=index + 2, column=col_idx).fill = red_fill

        wb.save(self.update_file_name)
        print(f"sheet: {SHEET_CODE_SUBMISSION_DETAIL}, 数据已更新到文件: {self.update_file_name}")

    def apply_styles(self):
        wb = load_workbook(self.update_file_name)

        for sheet_name in [SHEET_YESTERDAY_DETAIL, SHEET_YESTERDAY_DATA, SHEET_DAILY_DETAIL, SHEET_DAILY_USAGE, SHEET_CODE_SUBMISSION_DETAIL]:
            ws = wb[sheet_name]
            self.apply_styles_to_sheet(ws)

        wb.save(self.update_file_name)
        print(f"数据已更新到文件: {self.update_file_name}")

    def apply_styles_to_sheet(self, ws):
        font = Font(name='楷体', size=12, bold=False)
        header_font = Font(name='楷体', size=12, bold=True)
        alignment = Alignment(horizontal='center', vertical='center')
        border = Border(left=Side(style='thin'),
                        right=Side(style='thin'),
                        top=Side(style='thin'),
                        bottom=Side(style='thin'))

        for row in ws.iter_rows():
            for cell in row:
                cell.font = font
                cell.alignment = alignment
                cell.border = border
                if cell.row == 1 or (cell.row == 2 and isinstance(cell.value, tuple)):
                    cell.font = header_font

        for col in ws.columns:
            non_merged_cells = [cell for cell in col if not isinstance(cell, MergedCell)]
            if non_merged_cells:
                ws.column_dimensions[non_merged_cells[0].column_letter].width = 16

def main():
    yesterday = datetime.now() - timedelta(days=1)
    formatted_date = yesterday.strftime('%m-%d')
    file_name = f'爱码用户清单202501_202503 ({formatted_date}).xlsx'
    file_path = os.path.join(DATA_DIR, file_name)
    update_file_name = os.path.join(DATA_DIR, BASE_FILE_NAME)
    file_pattern = re.compile(FILE_NAME_PATTERN)

    updater = ExcelUpdater(file_path, update_file_name, formatted_date)
    updater.read_yesterday_data()
    updater.read_previous_day_data(DATA_DIR, file_pattern)
    updater.calculate_yesterday_detail()
    updater.read_personnel_info()
    updater.update_sheets()
    updater.apply_styles()

if __name__ == "__main__":
    main()